// app/db/entity/Activity.js
require('module-alias/register')
const BaseEntity = require('@db/base/baseEntity');
const logger = require('@utils/logger');
const colors = require('colors');

/**
 *  id INTEGER PRIMARY KEY AUTOINCREMENT,
 *  name TEXT NOT NULL,  活动名称
 *  serial TEXT DEFAULT '',  活动编号
 *  sign_amount INTEGER DEFAULT 0,  签到数量
 *  intro TEXT DEFAULT '',  活动描述或备注
 *  state INTEGER DEFAULT 1,  活动状态：1=未开始，2=进行中，3=已结束
 *  plugin_writer INTEGER DEFAULT 1,  手写框开启：1=开启，2=不开启
 *  plugin_camera INTEGER DEFAULT 1,  拍照功能：1=开启，2=不开启
 *  activity_mode INTEGER DEFAULT 1,  活动模式：1=签名，2=照片，3=微信签到(unreachable)
 *  photo_scale TEXT DEFAULT '400,300',  照片比例：为字符串，中间以「,」号分隔
 *  is_deleted INTEGER DEFAULT 1,  软删除标记
 *  activity_time DATETIME DEFAULT CURRENT_TIMESTAMP,  活动时间：
 *  activity_end DATETIME DEFAULT CURRENT_TIMESTAMP,  活动结束时间：
 *  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,  创建时间：
 *  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP   最后更新时间
 */
class Activity extends BaseEntity {
    constructor() {
        super('activities');    // 调用父类构造函数

        // this.createTable();
        // this.createIndexes();
        this.addMissingColumns();
        // 扩展状态枚举
        this.STATUS = {
            ...super.constructor.STATUS,
            DRAFT: 1,
            PUBLISHED: 2,
            OVER: 3,
        };
    }

    createTable() {
        const sql = `
            CREATE TABLE IF NOT EXISTS activities (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                serial TEXT DEFAULT '',
                sign_amount INTEGER DEFAULT 0,
                intro TEXT DEFAULT '',
                state INTEGER DEFAULT 1,              
                is_deleted INTEGER DEFAULT 1,
                activity_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        `;
        this.db.prepare(sql).run();
    }

    // 补全缺失字段
    addMissingColumns() {
        const requiredColumns = [
            {
                name: 'activity_end',
                type: "DATETIME", // 先不加默认值
                defaultValue: "CURRENT_TIMESTAMP" // 单独处理默认值
            }
        ];

        const existingColumns = this.db
            .prepare("PRAGMA table_info(activities)")
            .all()
            .map(col => col.name);

        requiredColumns.forEach(col => {
            if (!existingColumns.includes(col.name)) {
                try {
                    // 第一步：添加列（不带默认值）
                    const addColumnSql = `ALTER TABLE activities ADD COLUMN ${col.name} ${col.type}`;
                    this.db.prepare(addColumnSql).run();

                    // 第二步：设置默认值（对于新插入的记录）
                    if (col.defaultValue) {
                        // 对于现有记录，更新为默认值
                        const updateSql = `UPDATE activities SET ${col.name} = ${col.defaultValue}`;
                        this.db.prepare(updateSql).run();
                    }
                } catch (error) {
                    logger.error(`添加列 ${col.name} 失败: ${error.message}`);
                }
            }
        });
    }


    createIndexes() {
        // 为常用查询字段创建索引
        const indexes = [
            'CREATE INDEX IF NOT EXISTS idx_activities_name ON activities(name)',
            'CREATE INDEX IF NOT EXISTS idx_activities_serial ON activities(serial)',
            'CREATE INDEX IF NOT EXISTS idx_activities_state ON activities(state)',
            'CREATE INDEX IF NOT EXISTS idx_activities_is_deleted ON activities(is_deleted)',
            'CREATE INDEX IF NOT EXISTS idx_activities_activity_time ON activities(activity_time)'
        ];

        indexes.forEach(sql => this.db.prepare(sql).run());
    }

    // 扩展专属查询方法
    whereStartTime(start) {
        return this.query().where('start_time', '$gte', start);
    }

    whereEndTime(end) {
        return this.query().where('end_time', '$lte', end);
    }

    // 复杂业务查询示例
    async findActiveActivities(page, pageSize, conditions = {}) {
        let query = this.query();

        if (conditions.is_deleted) {
            query.where('is_deleted', '$eq', this.STATUS.DELETED);
        } else {
            query.where('is_deleted', '$eq', this.STATUS.DELETED);
        }

        if (conditions.state) {
            query.where('state', '$eq', conditions.state);
        }

        if (conditions.name) {
            query.where('name', '$like', conditions.name);
        }

        return query()
            .paginate(page, pageSize)
            .execute();
    }

    // Activity特有方法
    incrementSignAmount(id) {
        const sql = `UPDATE ${this.tableName} SET sign_amount = sign_amount + 1 WHERE id = ?`;
        return this.db.prepare(sql).run(id).changes;
    }

    insert(activity) {
        const sql = `
            INSERT INTO activities (
                name, serial, activity_time, activity_end,
                sign_amount, intro, state
            ) VALUES (
                @name, @serial, @activity_time, @activity_end,
                @sign_amount, @intro, @state
            )
        `;
        // console.log(`ActivityEntity.js 插入新活动调试：${JSON.stringify(activity)}`.blue)
        return this.db.prepare(sql).run(activity).lastInsertRowid;
    }

    update(activity) {
        const sql = `
            UPDATE activities SET
                name = @name,
                serial = @serial,               
                activity_time = @activity_time,
                activity_end = @activity_end,
                sign_amount = @sign_amount,
                intro = @intro,
                state = @state,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = @id
        `;
        return this.db.prepare(sql).run(activity).changes;
    }

    delete(id) {
        // 软删除
        const sql = `
            UPDATE activities SET
                is_deleted = 2,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        `;
        return this.db.prepare(sql).run(id).changes;
    }

    remove(id) {
        // 物理删除
        const sql = `DELETE FROM activities WHERE id = ?`;
        return this.db.prepare(sql).run(id).changes;
    }

    getById(id) {
        const sql = `SELECT * FROM activities WHERE id = ?`;
        return this.db.prepare(sql).get(id);
    }

    getAll() {
        const sql = `SELECT * FROM activities WHERE is_deleted = 1 ORDER BY activity_time DESC`;
        return this.db.prepare(sql).all();
    }

    // 获取进行中的活动
    getActivitiesByState(state) {
        const sql = `SELECT * FROM activities WHERE state = ? AND is_deleted = 1 ORDER BY activity_time DESC`;
        return this.db.prepare(sql).all(state);
    }

    /**
     * 按条件查询总记录数（用于分页计算总页数）
     * @param {Object} conditions 查询条件
     * @returns {number} 总记录数
     */
    countByCondition(conditions = {}) {
        // 构建 WHERE 子句
        const whereClauses = [];
        const params = [];

        // 处理各种查询条件
        if (conditions.name) {
            whereClauses.push('name LIKE ?');
            params.push(`%${conditions.name}%`);
        }

        if (conditions.serial) {
            whereClauses.push('serial = ?');
            params.push(conditions.serial);
        }

        if (conditions.state) {
            whereClauses.push('state = ?');
            params.push(conditions.state);
        }

        if (conditions.startTime) {
            whereClauses.push('activity_time >= ?');
            params.push(conditions.startTime);
        }

        if (conditions.endTime) {
            whereClauses.push('activity_time <= ?');
            params.push(conditions.endTime);
        }

        // 构建完整SQL
        const whereStr = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';
        const sql = `SELECT COUNT(*) as total FROM activities ${whereStr}`;

        // 执行查询
        const result = this.db.prepare(sql).get(params);
        return result ? result.total : 0;
    }

    batchUpdateState() {
        const sql = `
            UPDATE activities
            SET state = CASE
                WHEN datetime(activity_time) > datetime('now') THEN 1  -- 未开始（活动开始时间在未来）
                WHEN datetime('now') BETWEEN datetime(activity_time) AND datetime(activity_end) THEN 2  -- 进行中（当前时间在活动时间范围内）
                ELSE 3  -- 已结束（当前时间超过活动结束时间）
            END,
            updated_at = CURRENT_TIMESTAMP
        `;

        return this.db.prepare(sql).run()
    }

    // 关闭数据库连接
    close() {
        this.db.close();
    }
}

module.exports = Activity;


// 在文件底部添加（测试后删除）
if (require.main === module) {
    const activity = new Activity(':memory:'); // 内存数据库
    activity.insert({ name: 'Quick Test' });
    console.log(activity.getAll());
    activity.close();
}